import json
import openpyxl
import pandas as pd


excel_file = 'part_name-route.xlsx'
df = pd.read_excel(excel_file)

data = df.to_dict(orient='records')

json_file = 'part_name-route.json'
with open(json_file, 'w', encoding='utf-8') as f:
    json.dump(data, f, ensure_ascii=False, indent=4)

with open('part_name-route.json', 'r', encoding='utf-8') as f1:
    json1_data = json.load(f1)

with open('results.json', 'r', encoding='utf-8') as f2:
    json2_data = json.load(f2)

with open('subsystem.json', 'r', encoding='utf-8') as f3:
    json3_data = json.load(f3)

whitelist = [
    {"部件名": "acts", "路径": "validator/acts_validator"},
    {"部件名": "device_attest", "路径": "demo/hvigor_sample/demo_entry_feature"},
]

wb = openpyxl.Workbook()
wb.remove(wb["Sheet"])

sheet_no_action = wb.create_sheet(title='无需治理')
sheet_need_action = wb.create_sheet(title='需要治理')

sheet_no_action.append(['部件名', '路径'])
sheet_need_action.append(['部件名', '路径', '正确路径', 'subsystem'])

# 遍历JSON2的数据
for component, paths in json2_data.items():
    # 初始化正确路径和subsystem
    correct_path = None
    subsystem = None

    # 尝试匹配JSON1中的部件名以获取正确路径
    matched_item = next((item for item in json1_data if item['部件名'] == component), None)
    if matched_item:
        correct_path = matched_item['路径']

        # 检查json3中的subsystem映射
        for subsystem_key, subsystem_values in json3_data.items():
            if component in subsystem_values:
                subsystem = subsystem_key
                break

    # 遍历路径
    for path in paths:
        # 检查当前部件名和路径是否在白名单中
        if any(item['部件名'] == component and item['路径'] == path for item in whitelist):
            # 如果是白名单中的项，直接写入无需治理的sheet页
            sheet_no_action.append([component, path])
        elif matched_item and path.startswith(correct_path):
            # 如果路径以正确路径开头，也写入无需治理的sheet页
            sheet_no_action.append([component, path])
        else:
            # 否则写入需要治理的sheet页
            sheet_need_action.append([component, path, correct_path, subsystem])

wb.save('comparison_result.xlsx')
print("success")